/*
 * ConceptualDAO.java
 *
 * Created on March 22, 2007, 1:45 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.*;
import java.sql.*;
import java.util.ArrayList;
import edu.byu.isys413.group1E.data.*;
import edu.byu.isys413.group1E.handlers.*;

/**
 *
 * @author Kendall Frazier
 */
public class ConceptualDAO extends ProductDAO{
    private static ConceptualDAO instance = null;
    
    /** Creates a new instance of ConceptualDAO */
    public ConceptualDAO() {
    }
    
    /**Retrieves an instance of the TransactionDAO object**/
    public static synchronized ConceptualDAO getInstance() {
        if (instance == null) {
            instance = new ConceptualDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     *
     * @throws java.lang.Exception
     * @return
     */
    public ConceptualBO create() throws Exception{
        String id = GUID.generate();
        ConceptualBO bo = new ConceptualBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getProductID(), bo);
        return bo;
    }
    /**
     *This method reads in a ProductBO for the caller based upon its id(the internal identifyer).  This method opens the connection
     *to the database, adn then calls read(id, conn) to actually read the object in.
     *
     * @param id
     * @throws java.sql.SQLException
     * @throws edu.byu.isys413.group1E.DataException
     * @throws edu.byu.isys413.group1E.ConnectionPoolException
     * @return ProductBO the product that was requested by the caller
     */
    public ProductBO read(String id) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        ConceptualBO conceptual = (ConceptualBO)c.get(id);
        
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            conceptual = (ConceptualBO)this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return conceptual;
    }
    /**
     *This method is called to read in a ProductBO.  It is given a connection to the database, so doesn't worry about connections.
     *This method creates the object, puts it to cache, and then calls super.read(id, conn) so that the Product info can be loaded
     *into the object that isn't contained in teh conceptual_product table.  When the super.read(id, conn) is done, this class
     *populates the object with data from the conceptual_product table.
     *
     * @param id
     * @param conn
     * @throws java.sql.SQLException
     * @throws edu.byu.isys413.group1E.DataException
     * @throws edu.byu.isys413.group1E.ConnectionPoolException
     * @return
     */
    public synchronized ProductBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        ConceptualBO conceptual = (ConceptualBO)c.get(id);
        //put it to the cache so the super.read can get it
        if(conceptual == null){
            conceptual = new ConceptualBO(id);
        }        
        c.put(id, conceptual);
         //if not in the cache, get a result set from
         //a SELECT * FROM table WHERE id=guid
        conceptual = (ConceptualBO)super.read(id, conn);
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Conceptual_Product WHERE ProductID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            
            conceptual.setProductName(rs.getString("Product_Name"));
            conceptual.setProductID(rs.getString("ProductID"));
            conceptual.setDescription(rs.getString("Description"));
            conceptual.setAverageCost(rs.getDouble("average_cost"));
            conceptual.setManufacturer((ManufacturerBO)ManufacturerDAO.getInstance().read(rs.getString("manufacturer"), conn));
            conceptual.setWarranty((WarrantyBO)WarrantyDAO.getInstance().read(rs.getString("warranty"), conn));
            conceptual.setCategory(rs.getString("category"));
            
            
            // save to the cache
            c.put(conceptual.getProductID(), conceptual);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close(); ConnectionPool.getInstance().release(conn);
        // return the BO
        return conceptual;
    }
    
    public synchronized ArrayList<ConceptualBO> readAllConceptual() throws DataException, SQLException, ConnectionPoolException{
//        ProductDAO prodDAO = ProductDAO.getInstance();
        ArrayList<ConceptualBO> list = new ArrayList<ConceptualBO>();
//now build the query
        String query = "SELECT * FROM Conceptual_Product";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
//extract the elements from the result set and build a list of results
//build the object from the result set
        while (rs.next()) {
            ConceptualBO conceptual = new ConceptualBO(rs.getString("ProductID"));
            Cache.getInstance().put(rs.getString("ProductID"), conceptual);
            conceptual = (ConceptualBO)super.read(rs.getString("ProductID"), conn);
            
            conceptual.setProductName(rs.getString("Product_Name"));
            conceptual.setProductID(rs.getString("ProductID"));
            conceptual.setDescription(rs.getString("Description"));
            conceptual.setAverageCost(rs.getDouble("average_cost"));
            conceptual.setManufacturer((ManufacturerBO)ManufacturerDAO.getInstance().read(rs.getString("manufacturer")));
            conceptual.setWarranty((WarrantyBO)WarrantyDAO.getInstance().read(rs.getString("warranty")));
            conceptual.setCategory(rs.getString("category"));
            
            
//            Conceptual.setProduct(prodDAO.read(rs.getString("productID")));
            conceptual.setAlreadyInDB(true);
            conceptual.setDirty(false);
            
// save to the list
            list.add(conceptual);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
    /**
     *
     * @throws edu.byu.isys413.group1E.DataException
     * @throws java.sql.SQLException
     * @throws edu.byu.isys413.group1E.ConnectionPoolException
     * @return
     */
    public synchronized ArrayList<ProductBO> readAll() throws DataException, SQLException, ConnectionPoolException{
//        ProductDAO prodDAO = ProductDAO.getInstance();
        ArrayList<ProductBO> list = new ArrayList<ProductBO>();
//now build the query
        String query = "SELECT * FROM Conceptual_Product";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
//extract the elements from the result set and build a list of results
//build the object from the result set
        while (rs.next()) {
            ConceptualBO conceptual = new ConceptualBO(rs.getString("ProductID"));
            Cache.getInstance().put(rs.getString("ProductID"), conceptual);
            conceptual = (ConceptualBO)super.read(rs.getString("ProductID"), conn);
            
            conceptual.setProductName(rs.getString("Product_Name"));
            conceptual.setProductID(rs.getString("ProductID"));
            conceptual.setDescription(rs.getString("Description"));
            conceptual.setAverageCost(rs.getDouble("average_cost"));
            conceptual.setManufacturer((ManufacturerBO)ManufacturerDAO.getInstance().read(rs.getString("manufacturer")));
            conceptual.setWarranty((WarrantyBO)WarrantyDAO.getInstance().read(rs.getString("warranty")));
            conceptual.setCategory(rs.getString("category"));
            
            
//            Conceptual.setProduct(prodDAO.read(rs.getString("productID")));
            conceptual.setAlreadyInDB(true);
            conceptual.setDirty(false);
            
// save to the list
            list.add(conceptual);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
    
    public synchronized ArrayList<String> readCategories() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<ProductBO> list = new ArrayList<ProductBO>();
        //now build the query
        String query = ("SELECT category FROM Conceptual_Product");
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        ConnectionPool.getInstance().release(conn);
        
        //extract the elements from the result set and builds a list of results
        ArrayList<String> categoryList = new ArrayList<String>();
        while (rs.next()) {
            String temp = (rs.getString("Category"));
            if (categoryList.contains(temp)){
                categoryList.add(temp);
            }
        }
        return categoryList;
    }
    public synchronized ArrayList<String> readRentalCategories() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<ProductBO> list = new ArrayList<ProductBO>();
        //now build the query
        String query = ("SELECT * FROM Conceptual_Product WHERE forRent = 1");
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        ConnectionPool.getInstance().release(conn);
        
        //extract the elements from the result set and builds a list of results
        ArrayList<String> categoryList = new ArrayList<String>();
        while (rs.next()) {
            String temp = (rs.getString("Category"));
            if (categoryList.contains(temp)){
                categoryList.add(temp);
            }
        }
        return categoryList;
    }
    
    public synchronized ArrayList<ProductBO> readByCategory(String category) throws DataException, SQLException, ConnectionPoolException{
        ArrayList<ProductBO> list = new ArrayList<ProductBO>();
//now build the query
        String query = "SELECT * FROM Conceptual WHERE CATEGORY = '"+category+"'";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
//extract the elements from the result set and build a list of results
//build the object from the result set
        while (rs.next()) {
            ConceptualBO conceptual = new ConceptualBO(rs.getString("ProductID"));
            Cache.getInstance().put(rs.getString("ProductID"), conceptual);
            conceptual = (ConceptualBO)super.read(rs.getString("ProductID"), conn);
            
            conceptual.setProductID(rs.getString("ProductID"));
            conceptual.setDescription(rs.getString("Description"));
            conceptual.setAverageCost(rs.getDouble("average_cost"));
            conceptual.setManufacturer((ManufacturerBO)ManufacturerDAO.getInstance().read(rs.getString("manufacturer")));
            conceptual.setWarranty((WarrantyBO)WarrantyDAO.getInstance().read(rs.getString("warranty")));
            conceptual.setCategory(rs.getString("category"));
            
            
//            Conceptual.setProduct(prodDAO.read(rs.getString("productID")));
            conceptual.setAlreadyInDB(true);
            conceptual.setDirty(false);
            
// save to the list
            list.add(conceptual);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    /**
     *
     * @param xid
     * @throws edu.byu.isys413.group1E.DataException
     * @throws java.sql.SQLException
     * @throws edu.byu.isys413.group1E.ConnectionPoolException
     * @return
     */
    public synchronized ProductBO readBySKU(String xid) throws DataException, SQLException, ConnectionPoolException{
        
        String id;
        String query = "SELECT * FROM PRODUCT WHERE SKU = '" + xid + "'";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        ConnectionPool.getInstance().release(conn);
        
        if(rs.next()) {
            id = rs.getString("ProductID");
        }else{
            throw new DataException("Object was not found in the database.");
        }
        ConceptualBO conceptual = (ConceptualBO)ConceptualDAO.getInstance().read(id, conn);
        
        stmt.close(); ConnectionPool.getInstance().release(conn);
        return conceptual;
    }
    /**
     *
     * @param bo
     * @param conn
     * @throws java.sql.SQLException
     * @throws edu.byu.isys413.group1E.DataException
     * @throws edu.byu.isys413.group1E.ConnectionPoolException
     */
    protected void update(ProductBO bo, Connection conn) throws SQLException, DataException, ConnectionPoolException{
        //update the DB
        ConceptualBO BO = (ConceptualBO)bo;
        
//        super.save(BO, conn);
        
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Conceptual_Product SET ProductID = ?, " +
                "Description = ?, Average_Cost = ?, Warranty = ?, Manufacturer = ? WHERE ProductID = ?");
        
        pstmt.setString(1, BO.getProductID());
        pstmt.setString(2, BO.getDescription());
        pstmt.setDouble(3, BO.getAverageCost());
        pstmt.setString(4, BO.getWarranty().getId());
        pstmt.setString(5, BO.getManufacturer().getId());
        pstmt.setString(6, BO.getProductID());
        
        pstmt.executeUpdate();
        pstmt.close();
        BO.setAlreadyInDB(true);
    }// end update
    synchronized void insert(ProductBO bo, Connection conn) throws SQLException, DataException, ConnectionPoolException {
//update the DB
        ConceptualBO BO = (ConceptualBO)bo;
        
//        super.save(BO, conn);
        
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Conceptual_Product (PRODUCTID, description, average_cost, " +
                "warranty, manufacturer) VALUES (?, ?, ?, ?, ?)");
        
        pstmt.setString(1, BO.getProductID());
        pstmt.setString(2, BO.getDescription());
        pstmt.setDouble(3, BO.getAverageCost());
        pstmt.setString(4, BO.getWarranty().getId());
        pstmt.setString(5, BO.getManufacturer().getId());
        
        pstmt.executeUpdate();
        pstmt.close();
// tell the object that it's now in the db (so we call update next time not insert)
        BO.setAlreadyInDB(true);
    }//end insert
}
